﻿using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using InventoryToPredictAPI.model;
using Vertica.Data.VerticaClient;

namespace InventoryToPredictAPI
{
    /// <summary>
    /// 开店计划
    /// author Gavin Gu
    /// Date   20171225
    /// </summary>
    [MustLogin]
    public class ShopPlanningHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region 端头陈列量列表

        [MustLogin]
        [Description("端头陈列量列表")]
        [ReturnType(typeof(IList<ShopPlanning.ShopPlanningModel>))]
        public ApiResult GetShopPlanninglist(
            [ParamDescription("区域")] string Area = "",
            [ParamDescription("店铺级别")] string ShopLevel = "",
            [ParamDescription("开店时间")] string OpenDate = "",
            [ParamDescription("页码")] int Size = 1,
            [ParamDescription("页数")] int Page = 100)
        {

            try
            {
                var count = ReturnCount(Area, ShopLevel, OpenDate);//            dictName 
                var result = PaperData<IGrouping<string, ShopPlanning.ShopPlanningModel>>.Create(count, Size, Page);
                string sql = $@" SELECT sp.ExtrSource,sp.CompanyID, sp.OpenDate ,sp.ShopNum,gv.InfoName as dictName,gv.InfoID as dictValue
                                        ,eboa.OperationAreaName,eboa.OperationAreaCode,sp.ShopPlanID,sp.OperationAreaID,date('NOW') as NowTime 
                                    FROM ndw.ndw_ShopPlanning sp 
                                    INNER JOIN nods.nods_erp_Bas_OperationArea eboa ON eboa.OperationAreaID=sp.OperationAreaID AND eboa.ExtrSource=sp.ExtrSource AND eboa.CompanyID=sp.CompanyID
                                    LEFT JOIN  nods.nods_erp_SD_Bas_ShopAndCustBaseInfo gv ON  sp.ExtrSource = gv.ExtrSource /* AND sp.CompanyID = gv.CompanyID*/ AND gv.InfoID=sp.ShopLevel
                                    WHERE gv.InfoType='ShopLevel' AND sp.ExtrSource = '{UserInfo.UserExtrSource}' AND sp.CompanyID = '{UserInfo.UserCompanyId}'
                                    and eboa.Level=1
                                      {(string.IsNullOrWhiteSpace(Area) ? "" : $" AND eboa.OperationAreaID ='{Area}' ")} 
                                      {(string.IsNullOrWhiteSpace(ShopLevel) ? "" : $" AND gv.InfoID = '{ShopLevel}'")} 
                                      {(string.IsNullOrWhiteSpace(OpenDate) ? "" : $"  AND sp.OpenDate= '{OpenDate}'")} 
                                    ORDER BY eboa.OperationAreaID,sp.OpenDate DESC,sp.ShopNum ASC,ShopPlanID DESC
                                     LIMIT {Size} offset {(Page - 1.0) * Size}
                            ";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<ShopPlanning.ShopPlanningModel>.ConvertToModel(dt);
                var groupedList = list.GroupBy(p => p.CompanyID + p.OpenDate + p.ShopNum + p.NowTime + p.ShopPlanID);
                result.Items = groupedList.ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region 返回行数

        public int ReturnCount(string Area = "", string ShopLevel = "", string OpenDate = "")
        {
            try
            {
                var countSql = string.Empty;

                #region 查询时获取行数

                countSql = $@"SELECT count(1) FROM ndw.ndw_ShopPlanning sp 
                            INNER JOIN nods.nods_erp_Bas_OperationArea eboa ON eboa.OperationAreaID=sp.OperationAreaID AND eboa.ExtrSource=sp.ExtrSource AND eboa.CompanyID=sp.CompanyID
                            LEFT JOIN  ndw.ndw_global_dictionary gv ON  sp.ExtrSource = gv.ExtrSource AND sp.CompanyID = gv.CompanyID AND gv.dictValue=sp.ShopLevel
                            WHERE gv.parentDictValue='店铺级别' AND sp.ExtrSource = '{UserInfo.UserExtrSource}' AND sp.CompanyID = '{UserInfo.UserCompanyId}'
                            and eboa.Level=1
                              {(string.IsNullOrWhiteSpace(Area) ? "" : $" AND eboa.OperationAreaName ='{Area}' ")} 
                              {(string.IsNullOrWhiteSpace(ShopLevel) ? "" : $" AND gv.dictName = '{ShopLevel}'")} 
                              
                              {(string.IsNullOrWhiteSpace(OpenDate) ? "" : $"  AND sp.OpenDate= '{OpenDate}'")} 
                            ";
                //{ (string.IsNullOrWhiteSpace(OpenDate) ? "" : $"  AND to_char(sp.OpenDate,'YYYY-MM') = '{OpenDate.Substring(0, 7) }'")}
                #endregion 查询时获取行数

                var cmd = Helper.GetSqlStringCommand(countSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion 返回行数

        //查询区域列表
        [Description("区域下拉列表")]
        [ReturnType(typeof(IList<ShopPlanning.BasOperationAreaModel>))]
        public ApiResult GetBasOperationAreaList()
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            try
            {
                var sql = $@"select OperationAreaName,OperationAreaCode,OperationAreaID from nods.nods_erp_bas_operationArea where CompanyID='{UserInfo.UserExtrSource}' and Level=1";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var result = ModelConvertHelper<ShopPlanning.BasOperationAreaModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }



        #region 删除

        [JsonType]
        [Description("删除数据")]
        [ReturnType(typeof(IList<ShopPlanning.ShopPlanningModel>))]
        public ApiResult Remove(ShopPlanning.ShopPlanningDeleteList param)
        {

            if (param == null || param.List.Count < 1)
            {
                return Error($"删除失败!");
            }
            HashSet<string> DeleteSql = new HashSet<string>();
            foreach (var item in param.List)
            {
                string deleteSql = $@"Delete FROM ndw.ndw_ShopPlanning
                                    where ShopPlanID={item.ShopPlanID} AND OpenDate='{item.OpenDate}' AND
	                                ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}';";
                DeleteSql.Add(deleteSql);
            }
            StringBuilder strSql = new StringBuilder();
            foreach (var item in DeleteSql)
            {
                strSql.Append(item);
            }
            if (strSql.Length > 0)
            {
                List<CommandInfo> cmdList = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo { CommandText = strSql.ToString() };
                cmdList.Add(cmd);
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            return Error($"删除失败!");
        }

        #endregion 删除

        #region 新增

        [JsonType]
        [Description("新增")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Insert([ParamDescription("要插入的数据")] ShopPlanning.ShopPlanningSaveModel param)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }


            #region 检测该是否已存在，避免插入重复数据
            if (param.ShopPlanID != 0)
                return Error("数据不符合，请刷新重试！");


            if (AlreadyExists(param.Area, param.Level, param.OpenDate, 0))
            {
                return Error($"新增失败！\r\n 数据已存在！");
            }

            #endregion

            #region 插入新数据
            int ShopPlanID = 1;
            string countSql = $"select max(ShopPlanID) from ndw.ndw_ShopPlanning where ExtrSource='{UserInfo.UserExtrSource}' AND CompanyID='{UserInfo.UserCompanyId}' ";
            var cmd = Helper.GetSqlStringCommand(countSql);
            string spid = Helper.ExecuteScalar(cmd).ToString();
            if (!string.IsNullOrEmpty(spid))
                ShopPlanID = Convert.ToInt32(spid);

            string insertSql =
                $@"INSERT INTO ndw.ndw_ShopPlanning(ShopPlanID,ExtrSource, CompanyID, ShopNum, OpenDate, LoadTime, OperationAreaID, ShopLevel)
                VALUES({ShopPlanID + 1},'{UserInfo.UserExtrSource}', '{UserInfo.UserCompanyId}',{param.ShopNum}, '{param.OpenDate}', now(), '{param.Area}', '{param.Level}')";
            //list.Add(new CommandInfo(insertSql, null));

            VerticaCommand command = new VerticaCommand(insertSql);
            int n = Helper.ExecuteNonQuery(command);

            #endregion

            if (n > 0)
            {
                return Success(0);
            }
            return Error("插入失败,请重试");
        }

        private bool AlreadyExists(string Area, string Level, string OpenDate, int ShopPlanID)
        {

            string countSql = $@"SELECT count(1) FROM ndw.ndw_ShopPlanning sp 
                        WHERE sp.ExtrSource = '{UserInfo.UserExtrSource}' AND sp.CompanyID = '{UserInfo.UserCompanyId}'
                        AND sp.OperationAreaID ='{Area}' AND sp.ShopLevel = '{Level}' AND sp.OpenDate = '{OpenDate}' 
                         {(ShopPlanID == 0 ? "" : $" AND sp.ShopPlanID !='{ShopPlanID}'")} ";

            var cmd = Helper.GetSqlStringCommand(countSql);
            var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
            if (count > 0)
                return true;

            return false;


        }

        #endregion


        #region Update 修改

        [MustLogin]
        [JsonType]
        [Description("修改")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Update([ParamDescription("要插入的数据")] ShopPlanning.ShopPlanningSaveModel param)
        {

            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }


            #region 检测该是否已存在，避免插入重复数据
            if (param.ShopPlanID == 0)
                return Error("数据不符合，请刷新重试！");


            if (AlreadyExists(param.Area, param.Level, param.OpenDate, param.ShopPlanID))
            {
                return Error($"新增失败！\r\n 数据已存在！");
            }
            #endregion



            string Sql = $@"UPDATE ndw.ndw_ShopPlanning SET OpenDate='{param.OpenDate}',ShopNum={param.ShopNum},ShopLevel ='{param.Level}',OperationAreaID='{param.Area}' 
                    WHERE ShopPlanID ='{param.ShopPlanID}' AND ExtrSource='{UserInfo.UserExtrSource}' AND CompanyID='{UserInfo.UserCompanyId}'";
            VerticaCommand command = new VerticaCommand(Sql);
            int n = Helper.ExecuteNonQuery(command);
            if (n > 0)
            {
                return Success(0);
            }
            return Error("更新失败,请重试");
        }
        #endregion



    }


}
